/**
 * SQLite API 适配器
 * 提供与远程API兼容的本地数据库操作接口
 * 支持离线数据存储和同步
 * 遵循HTTP接口数据格式规范
 */

import database from '@/utils/database'
import { generateId, formatDate, paginate } from '@/mock/utils'
import { TODOS_CONFIG, HABITS_CONFIG, FINANCE_CONFIG, TIME_CONFIG } from '@/config'

// 确保数据库已初始化
let dbInitialized = false

async function ensureDbInit() {
  if (!dbInitialized) {
    await database.init()
    dbInitialized = true
  }
}

// 统一响应格式包装器
function createResponse(data = null, message = 'success', code = 0) {
  return {
    code,
    message,
    data
  }
}

// 统一错误响应
function createErrorResponse(message, code = 1004) {
  return {
    code,
    message,
    data: null
  }
}

// 统一分页响应格式
function createPaginationResponse(list, page, pageSize, total) {
  // 确保所有参数都有合理的默认值，避免 undefined 或 NaN
  const safeList = Array.isArray(list) ? list : []
  const safePage = parseInt(page) || 1
  const safePageSize = parseInt(pageSize) || 20
  const safeTotal = parseInt(total) || 0
  
  return createResponse({
    list: safeList,
    page: safePage,
    pageSize: safePageSize,
    total: safeTotal
  })
}

// 用户相关 SQLite API
export const userSqliteApi = {
  // 用户登录（本地验证）
  async login(data) {
    try {
      await ensureDbInit()
      const { username, password } = data
      
      const usersResult = await database.query(
        'SELECT * FROM users WHERE username = ? AND is_deleted = 0',
        [username]
      )
      
      // 检查查询是否成功
      if (usersResult.code !== 0) {
        return createErrorResponse(usersResult.message || '查询用户失败')
      }
      
      const users = usersResult.data || []
      if (users.length === 0) {
        return createErrorResponse('用户不存在', 1003)
      }
      
      // 简化的密码验证（实际项目中应该使用加密）
      const user = users[0]
      
      return createResponse({
        token: `sqlite_token_${user.id}_${Date.now()}`,
        user: {
          id: user.id,
          username: user.username,
          email: user.email,
          nickname: user.nickname,
          avatar: user.avatar
        }
      })
    } catch (error) {
      return createErrorResponse(error.message || '登录失败')
    }
  },
  
  // 用户注册
  async register(data) {
    try {
      await ensureDbInit()
      const { username, email, password, nickname } = data
      
      // 检查用户名是否已存在
      const existingUsersResult = await database.query(
        'SELECT id FROM users WHERE username = ? OR email = ? AND is_deleted = 0',
        [username, email]
      )
      
      // 检查查询是否成功
      if (existingUsersResult.code !== 0) {
        return createErrorResponse(existingUsersResult.message || '检查用户是否存在失败')
      }
      
      const existingUsers = existingUsersResult.data || []
      if (existingUsers.length > 0) {
        return createErrorResponse('用户名或邮箱已存在', 1001)
      }
      
      const userId = generateId()
      const now = new Date().toISOString()
      
      await database.insert('users', {
        id: userId,
        username,
        email,
        nickname: nickname || username,
        avatar: '',
        phone: '',
        gender: 0,
        birthday: '',
        bio: '',
        settings: JSON.stringify({}),
        created_at: now,
        updated_at: now,
        is_deleted: 0
      })
      
      return createResponse({
        token: `sqlite_token_${userId}_${Date.now()}`,
        user: {
          id: userId,
          username,
          email,
          nickname: nickname || username,
          avatar: ''
        }
      })
    } catch (error) {
      return createErrorResponse(error.message || '注册失败')
    }
  },
  
  // 获取用户信息
  async getUserInfo() {
    try {
      await ensureDbInit()
      // 简化实现，返回第一个用户
      const usersResult = await database.query(
        'SELECT * FROM users WHERE is_deleted = 0 LIMIT 1'
      )
      
      // 检查查询是否成功
      if (usersResult.code !== 0) {
        return createErrorResponse(usersResult.message || '查询用户信息失败')
      }
      
      const users = usersResult.data || []
      if (users.length === 0) {
        return createErrorResponse('用户不存在', 1003)
      }
      
      const user = users[0]
      return createResponse({
        id: user.id,
        username: user.username,
        email: user.email,
        nickname: user.nickname,
        avatar: user.avatar,
        phone: user.phone,
        gender: user.gender,
        birthday: user.birthday,
        bio: user.bio,
        settings: user.settings ? JSON.parse(user.settings) : {}
      })
    } catch (error) {
      return createErrorResponse(error.message || '获取用户信息失败')
    }
  },
  
  // 更新用户信息
  async updateUserInfo(data) {
    try {
      await ensureDbInit()
      const usersResult = await database.query(
        'SELECT id FROM users WHERE is_deleted = 0 LIMIT 1'
      )
      
      // 检查查询是否成功
      if (usersResult.code !== 0) {
        return createErrorResponse(usersResult.message || '查询用户失败')
      }
      
      const users = usersResult.data || []
      if (users.length === 0) {
        return createErrorResponse('用户不存在', 1003)
      }
      
      const userId = users[0].id
      const updateData = {
        ...data,
        updated_at: new Date().toISOString()
      }
      
      if (updateData.settings) {
        updateData.settings = JSON.stringify(updateData.settings)
      }
      
      await database.update('users', updateData, 'id = ?', [userId])
      
      const userInfoResult = await this.getUserInfo()
      return userInfoResult
    } catch (error) {
      return createErrorResponse(error.message || '更新用户信息失败')
    }
  }
}

// 习惯相关 SQLite API
export const habitsSqliteApi = {
  // 获取习惯列表
  async getHabitsList(params = {}) {
    try {
      await ensureDbInit()
      const {
        page = 1,
        pageSize = 20,
        category,
        isActive,
        search
      } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams = []
      
      if (category) {
        whereClause += ' AND category_id = ?'
        whereParams.push(category)
      }
      
      if (isActive !== undefined) {
        whereClause += ' AND is_active = ?'
        whereParams.push(isActive ? 1 : 0)
      }
      
      if (search) {
        whereClause += ' AND (name LIKE ? OR description LIKE ?)'
        whereParams.push(`%${search}%`, `%${search}%`)
      }
      
      const result = await database.paginate('habits', {
        page,
        pageSize,
        where: whereClause,
        whereParams,
        orderBy: 'created_at DESC'
      })
      
      // 检查数据库操作是否成功
      if (result.code !== 0) {
        return createErrorResponse(result.message || '获取习惯列表失败')
      }
      
      const paginationData = result.data || {}
      return createPaginationResponse(
        paginationData.list,
        paginationData.page,
        paginationData.pageSize,
        paginationData.total
      )
    } catch (error) {
      return createErrorResponse(error.message || '获取习惯列表失败')
    }
  },
  
  // 创建习惯
  async createHabit(data) {
    try {
      await ensureDbInit()
      const habitId = generateId()
      const now = new Date().toISOString()
      
      const habitData = {
        id: habitId,
        user_id: 'current_user', // 简化实现
        name: data.name,
        description: data.description || '',
        category_id: data.categoryId || '',
        icon: data.icon || '',
        color: data.color || '#3498db',
        type: data.type || 'daily',
        frequency: JSON.stringify(data.frequency || {}),
        target_value: data.targetValue || 1,
        unit: data.unit || '',
        reminder_enabled: data.reminderEnabled ? 1 : 0,
        reminder_time: data.reminderTime || '',
        is_active: 1,
        created_at: now,
        updated_at: now,
        is_deleted: 0
      }
      
      await database.insert('habits', habitData)
      
      return createResponse({
        id: habitId,
        ...data,
        isActive: true,
        createdAt: now,
        updatedAt: now
      })
    } catch (error) {
      return createErrorResponse(error.message || '创建习惯失败')
    }
  },
  
  // 更新习惯
  async updateHabit(id, data) {
    try {
      await ensureDbInit()
      const updateData = {
        ...data,
        updated_at: new Date().toISOString()
      }
      
      if (updateData.frequency) {
        updateData.frequency = JSON.stringify(updateData.frequency)
      }
      
      const rowsAffected = await database.update('habits', updateData, 'id = ? AND is_deleted = 0', [id])
      
      if (rowsAffected === 0) {
        return createErrorResponse('习惯不存在', 1003)
      }
      
      const detailResult = await this.getHabitDetail(id)
      return detailResult
    } catch (error) {
      return createErrorResponse(error.message || '更新习惯失败')
    }
  },
  
  // 删除习惯
  async deleteHabit(id) {
    try {
      await ensureDbInit()
      const rowsAffected = await database.delete('habits', 'id = ?', [id])
      
      if (rowsAffected === 0) {
        return createErrorResponse('习惯不存在', 1003)
      }
      
      return createResponse({ success: true })
    } catch (error) {
      return createErrorResponse(error.message || '删除习惯失败')
    }
  },
  
  // 获取习惯详情
  async getHabitDetail(id) {
    try {
      await ensureDbInit()
      const habitsResult = await database.query(
        'SELECT * FROM habits WHERE id = ? AND is_deleted = 0',
        [id]
      )
      
      // 检查查询是否成功
      if (habitsResult.code !== 0) {
        return createErrorResponse(habitsResult.message || '查询习惯详情失败')
      }
      
      const habits = habitsResult.data || []
      if (habits.length === 0) {
        return createErrorResponse('习惯不存在', 1003)
      }
      
      const habit = habits[0]
      return createResponse({
        id: habit.id,
        name: habit.name,
        description: habit.description,
        categoryId: habit.category_id,
        icon: habit.icon,
        color: habit.color,
        type: habit.type,
        frequency: habit.frequency ? JSON.parse(habit.frequency) : {},
        targetValue: habit.target_value,
        unit: habit.unit,
        reminderEnabled: habit.reminder_enabled === 1,
        reminderTime: habit.reminder_time,
        isActive: habit.is_active === 1,
        createdAt: habit.created_at,
        updatedAt: habit.updated_at
      })
    } catch (error) {
      return createErrorResponse(error.message || '获取习惯详情失败')
    }
  },
  
  // 标记习惯完成
  async markHabitComplete(id, data) {
    try {
      await ensureDbInit()
      const { date = new Date().toISOString().split('T')[0], value = 1, note = '' } = data
      
      const recordId = generateId()
      const now = new Date().toISOString()
      
      try {
        await database.insert('habit_records', {
          id: recordId,
          habit_id: id,
          date,
          value,
          note,
          created_at: now,
          updated_at: now,
          is_deleted: 0
        })
      } catch (error) {
        // 如果记录已存在，则更新
        await database.update(
          'habit_records',
          { value, note, updated_at: now },
          'habit_id = ? AND date = ? AND is_deleted = 0',
          [id, date]
        )
      }
      
      return createResponse({ success: true })
    } catch (error) {
      return createErrorResponse(error.message || '标记习惯完成失败')
    }
  },
  
  // 取消习惯完成
  async unmarkHabitComplete(id, data) {
    try {
      await ensureDbInit()
      const { date = new Date().toISOString().split('T')[0] } = data
      
      await database.delete(
        'habit_records',
        'habit_id = ? AND date = ?',
        [id, date]
      )
      
      return createResponse({ success: true })
    } catch (error) {
      return createErrorResponse(error.message || '取消习惯完成失败')
    }
  },
  
  // 获取习惯统计数据
  async getHabitStats(id, params = {}) {
    try {
      await ensureDbInit()
      const { startDate, endDate } = params
      
      let whereClause = 'habit_id = ? AND is_deleted = 0'
      let whereParams = [id]
      
      if (startDate) {
        whereClause += ' AND date >= ?'
        whereParams.push(startDate)
      }
      
      if (endDate) {
        whereClause += ' AND date <= ?'
        whereParams.push(endDate)
      }
      
      const recordsResult = await database.query(
        `SELECT * FROM habit_records WHERE ${whereClause} ORDER BY date DESC`,
        whereParams
      )
      
      // 检查查询是否成功
      if (recordsResult.code !== 0) {
        return createErrorResponse(recordsResult.message || '查询习惯统计数据失败')
      }
      
      const records = recordsResult.data || []
      const totalDays = records.length
      const totalValue = records.reduce((sum, record) => sum + record.value, 0)
      const avgValue = totalDays > 0 ? totalValue / totalDays : 0
      
      return createResponse({
        totalDays,
        totalValue,
        avgValue,
        records: records.map(record => ({
          date: record.date,
          value: record.value,
          note: record.note
        }))
      })
    } catch (error) {
      return createErrorResponse(error.message || '获取习惯统计失败')
    }
  },

  // 获取习惯历史记录
  async history(id, params = {}) {
    try {
      await ensureDbInit()
      const { 
        page = 1, 
        pageSize = 20, 
        startDate, 
        endDate 
      } = params
      
      let whereClause = 'habit_id = ? AND is_deleted = 0'
      let whereParams = [id]
      
      if (startDate) {
        whereClause += ' AND date >= ?'
        whereParams.push(startDate)
      }
      
      if (endDate) {
        whereClause += ' AND date <= ?'
        whereParams.push(endDate)
      }
      
      // 获取总数
      const countResult = await database.query(
        `SELECT COUNT(*) as count FROM habit_records WHERE ${whereClause}`,
        whereParams
      )
      
      // 检查查询是否成功
      if (countResult.code !== 0) {
        return createErrorResponse(countResult.message || '获取习惯历史记录总数失败')
      }
      
      const total = (countResult.data && countResult.data[0] && typeof countResult.data[0].count !== 'undefined') 
        ? countResult.data[0].count 
        : 0
      
      // 获取分页数据
      const offset = (page - 1) * pageSize
      const recordsResult = await database.query(
        `SELECT * FROM habit_records WHERE ${whereClause} ORDER BY date DESC LIMIT ? OFFSET ?`,
        [...whereParams, pageSize, offset]
      )
      
      // 检查查询是否成功
      if (recordsResult.code !== 0) {
        return createErrorResponse(recordsResult.message || '获取习惯历史记录失败')
      }
      
      const records = recordsResult.data || []
      
      return createPaginationResponse(
        records.map(record => ({
          id: record.id,
          habitId: record.habit_id,
          date: record.date,
          value: record.value,
          note: record.note,
          createdAt: record.created_at,
          updatedAt: record.updated_at
        })),
        page,
        pageSize,
        total
      )
    } catch (error) {
      return createErrorResponse(error.message || '获取习惯历史记录失败')
    }
  }
}

// 财务相关 SQLite API
export const financeSqliteApi = {
  // 获取账户列表
  async getAccounts(params = {}) {
    try {
      await ensureDbInit()
      const { page = 1, pageSize = 20, type, isActive } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams = []
      
      if (type) {
        whereClause += ' AND type = ?'
        whereParams.push(type)
      }
      
      if (isActive !== undefined) {
        whereClause += ' AND is_active = ?'
        whereParams.push(isActive ? 1 : 0)
      }
      
      const result = await database.paginate('finance_accounts', {
        page,
        pageSize,
        where: whereClause,
        whereParams,
        orderBy: 'created_at DESC'
      })
      
      // 检查数据库操作是否成功
      if (result.code !== 0) {
        return createErrorResponse(result.message || '获取账户列表失败')
      }
      
      const paginationData = result.data || {}
      return createPaginationResponse(
        paginationData.list,
        paginationData.page,
        paginationData.pageSize,
        paginationData.total
      )
    } catch (error) {
      return createErrorResponse(error.message || '获取账户列表失败')
    }
  },
  
  // 创建账户
  async createAccount(data) {
    try {
      await ensureDbInit()
      const accountId = generateId()
      const now = new Date().toISOString()
      
      const accountData = {
        id: accountId,
        user_id: 'current_user',
        name: data.name,
        type: data.type,
        balance: data.balance || 0,
        currency: data.currency || 'CNY',
        icon: data.icon || '',
        color: data.color || '#3498db',
        is_active: 1,
        created_at: now,
        updated_at: now,
        is_deleted: 0
      }
      
      await database.insert('finance_accounts', accountData)
      
      return createResponse({
        id: accountId,
        ...data,
        balance: data.balance || 0,
        isActive: true,
        createdAt: now,
        updatedAt: now
      })
    } catch (error) {
      return createErrorResponse(error.message || '创建账户失败')
    }
  },
  
  // 获取交易记录
  async getTransactions(params = {}) {
    try {
      await ensureDbInit()
      const {
        page = 1,
        pageSize = 20,
        accountId,
        type,
        categoryId,
        startDate,
        endDate
      } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams = []
      
      if (accountId) {
        whereClause += ' AND account_id = ?'
        whereParams.push(accountId)
      }
      
      if (type) {
        whereClause += ' AND type = ?'
        whereParams.push(type)
      }
      
      if (categoryId) {
        whereClause += ' AND category_id = ?'
        whereParams.push(categoryId)
      }
      
      if (startDate) {
        whereClause += ' AND date >= ?'
        whereParams.push(startDate)
      }
      
      if (endDate) {
        whereClause += ' AND date <= ?'
        whereParams.push(endDate)
      }
      
      const result = await database.paginate('finance_transactions', {
        page,
        pageSize,
        where: whereClause,
        whereParams,
        orderBy: 'date DESC, created_at DESC'
      })
      
      // 检查数据库操作是否成功
      if (result.code !== 0) {
        return createErrorResponse(result.message || '获取交易记录失败')
      }
      
      const paginationData = result.data || {}
      return createPaginationResponse(
        paginationData.list,
        paginationData.page,
        paginationData.pageSize,
        paginationData.total
      )
    } catch (error) {
      return createErrorResponse(error.message || '获取交易记录失败')
    }
  },
  
  // 创建交易记录
  async createTransaction(data) {
    try {
      await ensureDbInit()
      const transactionId = generateId()
      const now = new Date().toISOString()
      
      const transactionData = {
        id: transactionId,
        user_id: 'current_user',
        account_id: data.accountId,
        type: data.type,
        amount: data.amount,
        category_id: data.categoryId || '',
        description: data.description || '',
        date: data.date || now.split('T')[0],
        tags: JSON.stringify(data.tags || []),
        location: data.location || '',
        created_at: now,
        updated_at: now,
        is_deleted: 0
      }
      
      await database.insert('finance_transactions', transactionData)
      
      // 更新账户余额
      if (data.type === 'income') {
        await database.executeSql(
          'UPDATE finance_accounts SET balance = balance + ?, updated_at = ? WHERE id = ?',
          [data.amount, now, data.accountId]
        )
      } else if (data.type === 'expense') {
        await database.executeSql(
          'UPDATE finance_accounts SET balance = balance - ?, updated_at = ? WHERE id = ?',
          [data.amount, now, data.accountId]
        )
      }
      
      return createResponse({
        id: transactionId,
        ...data,
        createdAt: now,
        updatedAt: now
      })
    } catch (error) {
      return createErrorResponse(error.message || '创建交易记录失败')
    }
  },
  
  // 获取财务统计
  async getFinanceStats(params = {}) {
    try {
      await ensureDbInit()
      const { startDate, endDate, accountId } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams = []
      
      if (accountId) {
        whereClause += ' AND account_id = ?'
        whereParams.push(accountId)
      }
      
      if (startDate) {
        whereClause += ' AND date >= ?'
        whereParams.push(startDate)
      }
      
      if (endDate) {
        whereClause += ' AND date <= ?'
        whereParams.push(endDate)
      }
      
      const incomeResult = await database.query(
        `SELECT COALESCE(SUM(amount), 0) as total FROM finance_transactions WHERE ${whereClause} AND type = 'income'`,
        whereParams
      )
      
      // 检查查询是否成功
      if (incomeResult.code !== 0) {
        return createErrorResponse(incomeResult.message || '查询收入统计失败')
      }
      
      const expenseResult = await database.query(
        `SELECT COALESCE(SUM(amount), 0) as total FROM finance_transactions WHERE ${whereClause} AND type = 'expense'`,
        whereParams
      )
      
      // 检查查询是否成功
      if (expenseResult.code !== 0) {
        return createErrorResponse(expenseResult.message || '查询支出统计失败')
      }
      
      const incomeData = incomeResult.data || []
      const expenseData = expenseResult.data || []
      const totalIncome = (incomeData[0] && typeof incomeData[0].total !== 'undefined') ? incomeData[0].total : 0
      const totalExpense = (expenseData[0] && typeof expenseData[0].total !== 'undefined') ? expenseData[0].total : 0
      const netIncome = totalIncome - totalExpense
      
      return createResponse({
        totalIncome,
        totalExpense,
        netIncome,
        transactionCount: await this.getTransactionCount(whereClause, whereParams)
      })
    } catch (error) {
      return createErrorResponse(error.message || '获取财务统计失败')
    }
  },
  
  async getTransactionCount(whereClause, whereParams) {
    try {
      const result = await database.query(
        `SELECT COUNT(*) as count FROM finance_transactions WHERE ${whereClause}`,
        whereParams
      )
      
      // 检查查询是否成功
      if (result.code !== 0) {
        return 0
      }
      
      const data = result.data || []
      return (data[0] && typeof data[0].count !== 'undefined') ? data[0].count : 0
    } catch (error) {
      return 0
    }
  }
}

// 时间管理相关 SQLite API
export const timeSqliteApi = {
  // 获取时间记录
  async getTimeRecords(params = {}) {
    try {
      await ensureDbInit()
      const {
        page = 1,
        pageSize = 20,
        categoryId,
        startDate,
        endDate
      } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams = []
      
      if (categoryId) {
        whereClause += ' AND category_id = ?'
        whereParams.push(categoryId)
      }
      
      if (startDate) {
        whereClause += ' AND start_time >= ?'
        whereParams.push(startDate)
      }
      
      if (endDate) {
        whereClause += ' AND start_time <= ?'
        whereParams.push(endDate)
      }
      
      const result = await database.paginate('time_records', {
        page,
        pageSize,
        where: whereClause,
        whereParams,
        orderBy: 'start_time DESC'
      })
      
      // 检查数据库操作是否成功
      if (result.code !== 0) {
        return createErrorResponse(result.message || '获取时间记录失败')
      }
      
      const paginationData = result.data || {}
      return createPaginationResponse(
        paginationData.list,
        paginationData.page,
        paginationData.pageSize,
        paginationData.total
      )
    } catch (error) {
      return createErrorResponse(error.message || '获取时间记录失败')
    }
  },
  
  // 创建时间记录
  async createTimeRecord(data) {
    try {
      await ensureDbInit()
      const recordId = generateId()
      const now = new Date().toISOString()
      
      const recordData = {
        id: recordId,
        user_id: 'current_user',
        activity_name: data.activityName,
        category_id: data.categoryId || '',
        start_time: data.startTime,
        end_time: data.endTime || null,
        duration: data.duration || null,
        description: data.description || '',
        tags: JSON.stringify(data.tags || []),
        created_at: now,
        updated_at: now,
        is_deleted: 0
      }
      
      await database.insert('time_records', recordData)
      
      return createResponse({
        id: recordId,
        ...data,
        createdAt: now,
        updatedAt: now
      })
    } catch (error) {
      return createErrorResponse(error.message || '创建时间记录失败')
    }
  },
  
  // 获取时间统计
  async getTimeStats(params = {}) {
    try {
      await ensureDbInit()
      const { startDate, endDate, categoryId } = params
      
      let whereClause = 'is_deleted = 0 AND duration IS NOT NULL'
      let whereParams = []
      
      if (categoryId) {
        whereClause += ' AND category_id = ?'
        whereParams.push(categoryId)
      }
      
      if (startDate) {
        whereClause += ' AND start_time >= ?'
        whereParams.push(startDate)
      }
      
      if (endDate) {
        whereClause += ' AND start_time <= ?'
        whereParams.push(endDate)
      }
      
      const result = await database.query(
        `SELECT 
          COUNT(*) as recordCount,
          COALESCE(SUM(duration), 0) as totalDuration,
          COALESCE(AVG(duration), 0) as avgDuration
        FROM time_records WHERE ${whereClause}`,
        whereParams
      )
      
      // 检查查询是否成功
      if (result.code !== 0) {
        return createErrorResponse(result.message || '查询时间统计失败')
      }
      
      const data = result.data || []
      const stats = data[0] || {}
      
      return createResponse({
        recordCount: (typeof stats.recordCount !== 'undefined') ? stats.recordCount : 0,
        totalDuration: (typeof stats.totalDuration !== 'undefined') ? stats.totalDuration : 0,
        avgDuration: (typeof stats.avgDuration !== 'undefined') ? stats.avgDuration : 0
      })
    } catch (error) {
      return createErrorResponse(error.message || '获取时间统计失败')
    }
  }
}

// 待办事项相关 SQLite API
export const todosSqliteApi = {
  // 获取待办事项列表
  async getTodosList(params = {}) {
    try {
      await ensureDbInit()
      const {
        page = 1,
        pageSize = 20,
        status,
        categoryId,
        priority,
        isImportant,
        isUrgent,
        search
      } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams = []
      
      if (status) {
        whereClause += ' AND status = ?'
        whereParams.push(status)
      }
      
      if (categoryId) {
        whereClause += ' AND category_id = ?'
        whereParams.push(categoryId)
      }
      
      if (priority) {
        whereClause += ' AND priority = ?'
        whereParams.push(priority)
      }
      
      if (isImportant !== undefined) {
        whereClause += ' AND is_important = ?'
        whereParams.push(isImportant ? 1 : 0)
      }
      
      if (isUrgent !== undefined) {
        whereClause += ' AND is_urgent = ?'
        whereParams.push(isUrgent ? 1 : 0)
      }
      
      if (search) {
        whereClause += ' AND (title LIKE ? OR description LIKE ?)'
        whereParams.push(`%${search}%`, `%${search}%`)
      }
      
      const result = await database.paginate('todos', {
        page,
        pageSize,
        where: whereClause,
        whereParams,
        orderBy: 'created_at DESC'
      })
      
      // 检查数据库操作是否成功
      if (result.code !== 0) {
        return createErrorResponse(result.message || '获取待办事项列表失败')
      }
      
      const paginationData = result.data || {}
      return createPaginationResponse(
        paginationData.list,
        paginationData.page,
        paginationData.pageSize,
        paginationData.total
      )
    } catch (error) {
      return createErrorResponse(error.message || '获取待办事项列表失败')
    }
  },
  
  // 创建待办事项
  async createTodo(data) {
    try {
      await ensureDbInit()
      const todoId = generateId()
      const now = new Date().toISOString()
      
      const todoData = {
        id: todoId,
        user_id: 'current_user',
        title: data.title,
        description: data.description || '',
        category_id: data.categoryId || '',
        priority: data.priority || 'medium',
        status: 'pending',
        tags: JSON.stringify(data.tags || []),
        due_date: data.dueDate || null,
        reminder: JSON.stringify(data.reminder || null),
        is_important: data.isImportant ? 1 : 0,
        is_urgent: data.isUrgent ? 1 : 0,
        estimated_duration: data.estimatedDuration || null,
        actual_duration: null,
        progress: 0,
        completed_at: null,
        created_at: now,
        updated_at: now,
        is_deleted: 0
      }
      
      await database.insert('todos', todoData)
      
      return createResponse({
        id: todoId,
        ...data,
        status: 'pending',
        progress: 0,
        createdAt: now,
        updatedAt: now
      })
    } catch (error) {
      return createErrorResponse(error.message || '创建待办事项失败')
    }
  },
  
  // 更新待办事项
  async updateTodo(id, data) {
    try {
      await ensureDbInit()
      const updateData = {
        ...data,
        updated_at: new Date().toISOString()
      }
      
      if (updateData.tags) {
        updateData.tags = JSON.stringify(updateData.tags)
      }
      
      if (updateData.reminder) {
        updateData.reminder = JSON.stringify(updateData.reminder)
      }
      
      if (updateData.isImportant !== undefined) {
        updateData.is_important = updateData.isImportant ? 1 : 0
        delete updateData.isImportant
      }
      
      if (updateData.isUrgent !== undefined) {
        updateData.is_urgent = updateData.isUrgent ? 1 : 0
        delete updateData.isUrgent
      }
      
      const rowsAffected = await database.update('todos', updateData, 'id = ? AND is_deleted = 0', [id])
      
      if (rowsAffected === 0) {
        return createErrorResponse('待办事项不存在', 1003)
      }
      
      return createResponse({ success: true })
    } catch (error) {
      return createErrorResponse(error.message || '更新待办事项失败')
    }
  },
  
  // 删除待办事项
  async deleteTodo(id) {
    try {
      await ensureDbInit()
      const rowsAffected = await database.delete('todos', 'id = ?', [id])
      
      if (rowsAffected === 0) {
        return createErrorResponse('待办事项不存在', 1003)
      }
      
      return createResponse({ success: true })
    } catch (error) {
      return createErrorResponse(error.message || '删除待办事项失败')
    }
  },
  
  // 标记待办事项完成
  async markTodoComplete(id) {
    try {
      await ensureDbInit()
      const now = new Date().toISOString()
      
      const rowsAffected = await database.update(
        'todos',
        {
          status: 'completed',
          progress: 100,
          completed_at: now,
          updated_at: now
        },
        'id = ? AND is_deleted = 0',
        [id]
      )
      
      if (rowsAffected === 0) {
        return createErrorResponse('待办事项不存在', 1003)
      }
      
      return createResponse({ success: true })
    } catch (error) {
      return createErrorResponse(error.message || '标记待办事项完成失败')
    }
  },
  
  // 取消待办事项完成
  async unmarkTodoComplete(id) {
    try {
      await ensureDbInit()
      const now = new Date().toISOString()
      
      const rowsAffected = await database.update(
        'todos',
        {
          status: 'pending',
          progress: 0,
          completed_at: null,
          updated_at: now
        },
        'id = ? AND is_deleted = 0',
        [id]
      )
      
      if (rowsAffected === 0) {
        return createErrorResponse('待办事项不存在', 1003)
      }
      
      return createResponse({ success: true })
    } catch (error) {
      return createErrorResponse(error.message || '取消待办事项完成失败')
    }
  },
  
  // 获取待办事项统计
  async getTodoStats(params = {}) {
    try {
      await ensureDbInit()
      const { startDate, endDate } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams = []
      
      if (startDate) {
        whereClause += ' AND created_at >= ?'
        whereParams.push(startDate)
      }
      
      if (endDate) {
        whereClause += ' AND created_at <= ?'
        whereParams.push(endDate)
      }
      
      const totalResult = await database.query(
        `SELECT COUNT(*) as total FROM todos WHERE ${whereClause}`,
        whereParams
      )
      
      // 检查查询是否成功
      if (totalResult.code !== 0) {
        return createErrorResponse(totalResult.message || '查询待办事项总数失败')
      }
      
      const completedResult = await database.query(
        `SELECT COUNT(*) as completed FROM todos WHERE ${whereClause} AND status = 'completed'`,
        whereParams
      )
      
      // 检查查询是否成功
      if (completedResult.code !== 0) {
        return createErrorResponse(completedResult.message || '查询已完成待办事项数量失败')
      }
      
      const pendingResult = await database.query(
        `SELECT COUNT(*) as pending FROM todos WHERE ${whereClause} AND status = 'pending'`,
        whereParams
      )
      
      // 检查查询是否成功
      if (pendingResult.code !== 0) {
        return createErrorResponse(pendingResult.message || '查询待处理待办事项数量失败')
      }
      
      const totalData = totalResult.data || []
      const completedData = completedResult.data || []
      const pendingData = pendingResult.data || []
      
      const total = (totalData[0] && typeof totalData[0].total !== 'undefined') ? totalData[0].total : 0
      const completed = (completedData[0] && typeof completedData[0].completed !== 'undefined') ? completedData[0].completed : 0
      const pending = (pendingData[0] && typeof pendingData[0].pending !== 'undefined') ? pendingData[0].pending : 0
      const completionRate = total > 0 ? (completed / total * 100).toFixed(2) : 0
      
      return createResponse({
        total,
        completed,
        pending,
        completionRate: parseFloat(completionRate)
      })
    } catch (error) {
      return createErrorResponse(error.message || '获取待办事项统计失败')
    }
  }
}

// 统计相关 SQLite API
export const statsSqliteApi = {
  // 获取综合统计
  async getOverallStats(params = {}) {
    try {
      await ensureDbInit()
      const { startDate, endDate } = params
      
      const [habitStats, financeStats, todoStats, timeStats] = await Promise.all([
        habitsSqliteApi.getHabitStats('all', { startDate, endDate }),
        financeSqliteApi.getFinanceStats({ startDate, endDate }),
        todosSqliteApi.getTodoStats({ startDate, endDate }),
        timeSqliteApi.getTimeStats({ startDate, endDate })
      ])
      
      return createResponse({
        habits: habitStats,
        finance: financeStats,
        todos: todoStats,
        time: timeStats
      })
    } catch (error) {
      return createErrorResponse(error.message || '获取综合统计失败')
    }
  }
}

// 设置相关 SQLite API
export const settingsSqliteApi = {
  // 获取用户设置
  async getUserSettings() {
    try {
      return await userSqliteApi.getUserInfo()
    } catch (error) {
      return createErrorResponse(error.message || '获取用户设置失败')
    }
  },
  
  // 更新用户设置
  async updateUserSettings(data) {
    try {
      return await userSqliteApi.updateUserInfo(data)
    } catch (error) {
      return createErrorResponse(error.message || '更新用户设置失败')
    }
  },
  
  // 数据备份
  async backupData() {
    try {
      await ensureDbInit()
      const result = await database.backup()
      return createResponse(result)
    } catch (error) {
      return createErrorResponse(error.message || '数据备份失败')
    }
  },
  
  // 数据恢复
  async restoreData(data) {
    try {
      await ensureDbInit()
      const result = await database.restore(data)
      return createResponse(result)
    } catch (error) {
      return createErrorResponse(error.message || '数据恢复失败')
    }
  },
  
  // 清空数据
  async clearData() {
    try {
      await ensureDbInit()
      const tables = ['habits', 'habit_records', 'finance_accounts', 'finance_transactions', 'time_records', 'todos']
      
      for (const table of tables) {
        await database.truncate(table)
      }
      
      return createResponse({ success: true })
    } catch (error) {
      return createErrorResponse(error.message || '清空数据失败')
    }
  }
}

// 上传相关 SQLite API（本地存储）
export const uploadSqliteApi = {
  // 上传头像（保存到本地）
  async uploadAvatar(filePath) {
    try {
      // 简化实现，返回本地路径
      return createResponse({
        url: filePath,
        filename: filePath.split('/').pop()
      })
    } catch (error) {
      return createErrorResponse(error.message || '上传头像失败')
    }
  },
  
  // 上传图片
  async uploadImage(filePath, formData = {}) {
    try {
      return createResponse({
        url: filePath,
        filename: filePath.split('/').pop(),
        size: 0 // 实际项目中可以获取文件大小
      })
    } catch (error) {
      return createErrorResponse(error.message || '上传图片失败')
    }
  },
  
  // 上传文件
  async uploadFile(filePath, formData = {}) {
    try {
      return createResponse({
        url: filePath,
        filename: filePath.split('/').pop(),
        size: 0
      })
    } catch (error) {
      return createErrorResponse(error.message || '上传文件失败')
    }
  }
}

export default {
  user: userSqliteApi,
  habits: habitsSqliteApi,
  finance: financeSqliteApi,
  time: timeSqliteApi,
  todos: todosSqliteApi,
  stats: statsSqliteApi,
  settings: settingsSqliteApi,
  upload: uploadSqliteApi
}